# @Author: 唐奇才
# @Time: 2021/6/13 22:41
# @File: comments.py
# @Software: PyCharm
import pymysql
import requests
from index import get_html


# 第(2.2)步
def save2mysql(data):
    """
    INSERT INTO `b-video-stat`.`relies`(`rpid`, `rcount`, `like`, `uname`, `sex`, `sign`, `avatar`, `current_level`, `message`) VALUES ('1', '1', '1', '1', '1', '1', '1', '1', '1')
    """
    try:
        conn = pymysql.connect(host="localhost", user="root",
                               password="1234", db="b-video-stat", port=3306)
        cur = conn.cursor()
        sql = """
            INSERT INTO `b-video-stat`.`relies`
            (`rpid`,`oid` , `rcount`, `like`, `uname`, `sex`, `sign`, `avatar`, `current_level`, `message`) 
            VALUES 
            ({})
        """.format(data)

        row = cur.execute(sql)
        if row == 1:
            # print(data[1], end=":")
            print("插入成功！", end="->")

        conn.commit()

        cur.close()
        conn.close()
    except Exception as e:
        print('出错了：', e)


# 第（2.1）步
def insert_relies(data, aid):
    try:
        conn = pymysql.connect(host="localhost", user="root",
                               password="1234", db="b-video-stat", port=3306)
        cur = conn.cursor()
        sql = """
               UPDATE `b-video-stat`.`ranks` SET `relies` = '{}' WHERE `aid` = '{}'
           """.format(data, aid)

        cur.execute(sql)
        conn.commit()
        cur.close()
        conn.close()
    except Exception as e:
        print('出错了：', e)


# 第（2）步
def get_comment_detail(url, page, oid) -> bool:
    res = get_html(url, st=0.01).json()

    if res["message"] == "评论区已关闭":
        return True
    data = res["data"]["replies"]
    infos = []
    try:
        if page == 1:
            all_relies = res['data']['cursor']['all_count']
            insert_relies(all_relies, oid)

        for d in data:
            inf = []
            inf.append(d['rpid']),
            inf.append(d['oid']),
            inf.append(d['rcount']),
            inf.append(d['like']),
            inf.append(d['member']["uname"]),
            inf.append(d['member']["sex"]),
            inf.append(d['member']["sign"]),
            inf.append(d['member']["avatar"]),
            inf.append(d['member']["level_info"]['current_level']),
            inf.append(str(d['content']['message']).replace("\n", "").replace(" ", ""))
            infos.append(inf)

        for info in infos:
            info = "'" + "','".join([str(i) for i in info]) + "'"
            save2mysql(info)

        print(res['data']['cursor']['is_end'])
        return res['data']['cursor']['is_end']
    except:
        return res['data']['cursor']['is_end']


# 第（1）步
def get_aid() -> list:
    try:
        conn = pymysql.connect(host="localhost", user="root",
                               password="1234", db="b-video-stat", port=3306)
        cur = conn.cursor()
        sql = """
               SELECT aid FROM ranks WHERE relies IS null
           """

        cur.execute(sql)
        conn.commit()
        data = cur.fetchall()
        data = [d[0] for d in data]
        cur.close()
        conn.close()
        return data
    except Exception as e:
        print('出错了：', e)


# 第(0)步
def main():
    oids = get_aid()
    total = len(oids)
    count = 1
    for oid in oids:
        print(oid)
        print("开始第：", end="")
        for i in range(1, 11):
            print(i, "页", end=" ")
            base_url = "https://api.bilibili.com/x/v2/reply/main?callback=jQuery17202098277853868633_1623595294806&type=1&oid={}&next={}" \
                .format(oid, i)
            is_end = get_comment_detail(base_url, i, oid)
            if is_end:
                break
        print("爬取进度：{}%".format(round((count / total), 3) * 100))
        count += 1


if __name__ == '__main__':
    main()
